package crm.base.dialect;

/**
 * SqlServer的分页方言(适用于SqlServer2005及以上)
 * 
 * @author Zhang Qiongbiao
 *
 */
public class SQLServerDialect extends Dialect {
	
	@Override
	protected String pagingSqlParser(String sql) {
		return pagingSqlParser(sql, offset, limit);
	}
	
	@Override
	protected String totalCountSqlParser(String sql) {
		return countSqlParser(sql);
	}
	
	/**
	 * 生成分页sql类似如下:
	 * 
	 * <pre>
	 * select * from (
	 *  select row_number () over (order by __tempcolumn__) as __rownum__, * from (
	 *   select top (:offset + :limit) __tempcolumn__ = 0, [column(s)] from [Table] order by [column] asc|desc
	 *  ) __t__ 
	 * ) __tt__ where __rownum__ > :offset
	 * </pre>
	 * 
	 * 取值区间: [offset, offset + limit)
	 * 
	 * @param sql
	 * @param offset
	 * @param limit
	 * @return
	 */
	private static String pagingSqlParser(String sql, int offset, int limit) {
		StringBuffer pagingsql = new StringBuffer();
		pagingsql.append("select * from ( select row_number () over (order by __tempcolumn__) as __rownum__, * from ( ");
		pagingsql.append(sql).insert(92 + getAfterSelectInsertPoint(sql), " top " + (offset + limit - 1) + " __tempcolumn__ = 0,");
		pagingsql.append(" ) __t__ ) __tt__ where __rownum__ >= ").append(offset);
		
		return pagingsql.toString();
	}
	
	/**
	 * 获取totalcount的sql解析,如下
	 * <pre>
	 * select count(1) from (
	 * 	select top 100 percent [distinct] [column(s)] from [Table] order by [column] asc|desc
	 * ) t
	 * </pre>
	 * 
	 * @param sql
	 * @return
	 */
	private static String countSqlParser(String sql) {
		StringBuffer countsql = new StringBuffer();
		
		countsql.append("select count(1) from ( ");
		countsql.append(sql).insert(23 + getAfterSelectInsertPoint(sql), " top 100 percent ");
		countsql.append(") t");
		
		return countsql.toString();
	}
	
	private static int getAfterSelectInsertPoint(String sql) {
		sql = sql.toLowerCase();
		int selectIndex = sql.indexOf("select");
		int selectDistinctIndex = sql.indexOf("select distinct");
		return (selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6));
	}
	
	public static void main(String[] args) {
		String sql = "select distinct NoteId, Notes from Note order by NoteId desc";
		String sql1 = "select * from @fastpage(select * from ServiceOrder order by ServiceOrderId desc) a inner join NewServiceRequest b on a.ServiceRequestId = b.NewServiceRequestId";
		int offset = 1001;
		int limit = 20;
		
		Dialect dialect = new SQLServerDialect();
		dialect.init(sql, offset, limit);
		System.out.println(dialect.getLimitString());
		System.out.println(dialect.getTotalCountString());
		dialect.init(sql1, offset, limit);
		System.out.println(dialect.getLimitString());
		System.out.println(dialect.getTotalCountString());
		
	}
}
